/***********************************************************************************************************************************
 *    Name: DBAdmin_Replication.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Execute on Distributor.
**/
USE DistributionDB;

--================================================================================================================================--
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'DBAdmin') BEGIN
	-- Delete all existing DBAdmin objects.
	DECLARE @tsql_command NVARCHAR(MAX); SET @tsql_command= N'';
	SELECT @tsql_command= @tsql_command + N'DROP ' + CASE type WHEN 'P' THEN 'PROCEDURE [' WHEN 'V' THEN 'VIEW [' ELSE 'FUNCTION [' END + SCHEMA_NAME(schema_id) + '].[' + name + ']; '
	  FROM sys.objects WHERE schema_id = SCHEMA_ID('DBAdmin') AND type IN ('P','V','FN','IF','TF')
	EXECUTE (@tsql_command);
END
	-- Create the DBAdmin schema if it doesn't already exist.
ELSE EXECUTE ('CREATE SCHEMA DBAdmin AUTHORIZATION dbo;');
GO

--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary: Subscriptions Inventory
CREATE VIEW DBAdmin.Subscriptions AS
SELECT DISTINCT
	ss.name AS subscriber,
	mss.subscriber_db,
	ps.name AS publisher,
	msp.publisher_db,
	msp.publication
  FROM dbo.MSsubscriptions mss
	INNER JOIN dbo.MSpublications msp ON (mss.publisher_id = msp.publisher_id AND mss.publication_id = msp.publication_id)
	INNER JOIN sys.servers ps ON (mss.publisher_id = ps.server_id)
	INNER JOIN sys.servers ss ON (mss.subscriber_id = ss.server_id)
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary: Subscription Performance Data
CREATE VIEW DBAdmin.MSReplication_MonitorData AS 
SELECT
	subscriber	= ISNULL(a.subscriber_name, s.name),
	m.publication,
	status		= CASE m.status WHEN 1 THEN N'Started' WHEN 2 THEN N'Succeeded' WHEN 3 THEN N'In Progress' WHEN 4 THEN N'Idle' WHEN 5 THEN N'Retrying' WHEN 6 THEN N'Failed' ELSE N'UNKNOWN' END,
	m.last_distsync,
	sync_age	= DATEDIFF(mi, m.last_distsync, CURRENT_TIMESTAMP),
	m.cur_latency
  FROM dbo.MSReplication_MonitorData m
	INNER JOIN dbo.MSDistribution_Agents AS a ON (
		m.publisher_srvid = a.publisher_id AND
		m.publisher_db = a.publisher_db COLLATE DATABASE_DEFAULT AND
		m.job_id = CAST(a.job_id AS UNIQUEIDENTIFIER) AND
		(m.publication = a.publication COLLATE DATABASE_DEFAULT OR a.publication = 'ALL' COLLATE DATABASE_DEFAULT))
	INNER JOIN master.sys.servers as s on (a.subscriber_id = s.server_id)
GO



-- Watchdog ========================================================================================================================
USE msdb;
DECLARE
	@JobName		SYSNAME			= N'» Watchdog: Replication',
	@OutputFileName	NVARCHAR(255)	= master.DBAdmin.GetInstancePath(N'WorkingDirectory') + N'Watchdog_Replication.txt',
	@jobID			BINARY(16);

SELECT @jobID= job_id FROM msdb.dbo.sysjobs WHERE name = @JobName;
IF @jobID IS Null BEGIN
	EXECUTE msdb.dbo.sp_add_job
		@job_name			= @JobName,
		@category_name		= N'DBAdmin',
		@owner_login_name	= N'sa',
		@job_id				= @jobID OUTPUT;
	EXECUTE msdb.dbo.sp_add_jobserver @job_id= @jobID, @server_name= N'(LOCAL)';
END ELSE EXECUTE msdb.dbo.sp_delete_jobstep @job_id= @jobID, @step_id= 0;

EXECUTE msdb.dbo.sp_update_job
	@job_id						= @jobID,
	@enabled					= 1,
	@notify_level_page			= 0,	-- Never
	@notify_level_netsend		= 0,	-- Never
	@notify_level_eventlog		= 0,	-- Never
	@notify_level_email			= 2,	-- On Failure
	@notify_email_operator_name	= N'(DB)A-Team';

EXECUTE msdb.dbo.sp_add_jobstep
	@job_id				= @jobID,
	@database_name		= N'DistributionDB',
	@step_name			= N'Watchdog Replication',
	@command			= N'EXECUTE sys.sp_replmonitorrefreshjob @iterations= 1;
SET CONCAT_NULL_YIELDS_NULL ON;
DECLARE @table NVARCHAR(MAX)= N''<table><tr><th style="text-align:left">Subscriber</th><th style="text-align:left">Publication</th><th>Sync Age</th></tr>'' + (
	SELECT
		td= subscriber,
		td= publication,
		td= sync_age
	  FROM DBAdmin.MSReplication_MonitorData
	  WHERE 12 < sync_age
	  ORDER BY sync_age DESC, cur_latency DESC
	  FOR XML RAW(''tr''), ELEMENTS) + N''</TABLE>'';
IF @table IS NOT Null EXECUTE msdb.dbo.sp_send_dbmail
	@recipients	= N''dbanotifications@bbaaviation.net'',
	@subject		= N''Watchdog Alert: Replication Failure(s)'',
	@importance	= N''High'',
	@body		= @table,
	@body_format	= ''HTML'';',
	@output_file_name	= @OutputFileName;

-- Schedule ========================================================================================================================
BEGIN TRY;
  EXECUTE msdb.dbo.sp_delete_schedule @schedule_name= @JobName, @force_delete= 1;
END TRY BEGIN CATCH END CATCH;
EXECUTE msdb.dbo.sp_add_schedule
	@schedule_name			= @JobName,
	@enabled				= 1,
	@freq_type				= 4,
	@freq_interval			= 1,
	@freq_subday_type		= 4,
	@freq_subday_interval	= 20,
	@freq_relative_interval	= 0,
	@freq_recurrence_factor	= 1,
	@active_start_date		= 20120920,
	@active_end_date		= 99991231,
	@active_start_time		= 080000,
	@active_end_time		= 220000;
BEGIN TRY;
  EXECUTE msdb.dbo.sp_attach_schedule @job_name= @JobName, @schedule_name= @JobName;
END TRY BEGIN CATCH END CATCH;

GO